package com.info.zhiduoduo.repository.mapper.company;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.entity.company.Company;
import com.info.zhiduoduo.repository.entity.company.CompanyEntrustFile;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author Bob
 * @since 2021-11-04
 */
public interface CompanyEntrustFileMapper extends BaseMapper<CompanyEntrustFile> {

	@Select(" select cef.* " +
			" from zdd_company_entrust_file cef " +
			" join zdd_company c on c.compid = cef.compid " +
			" where cef.compid = #{compid} " +
			" and cef.audit_state = 2 " +
			" and cef.deleted = 0" +
			" order by cef.entrust_end_time desc " +
			" limit 1 ")
	CompanyEntrustFile getByCompid(int compid);

	@Select(" select count(1)" +
			" from zdd_company_entrust_file cef " +
			" where cef.audit_state = 1 and cef.deleted = 0")
	Integer getAuditNum();

	@Select(" select cef.*, c.compname " +
			" from zdd_company_entrust_file cef " +
			" join zdd_company c on c.compid = cef.compid " +
			" where cef.compid = #{compId}" +
			" and cef.deleted = 0" +
			" and if(#{workType} = 0,1=1,cef.work_type=#{workType})" +
			" order by cef.id desc ")
	List<CompanyEntrustFile> getEntrustFileList(Integer compId,int workType);

	@Select(" select cef.*, c.compname,ccp.company_name as partyName,if(cef.work_type = 1, c.base_tax_rate, c.base_tax_rate_odd) as taxRate," +
			" bm.business_manager as merchandiserName,zbm.business_manager as corporateConsultant " +
			" FROM zdd_company_entrust_file cef " +
			" JOIN zdd_company c on c.compid = cef.compid " +
			" LEFT JOIN zdd_company_account ca ON cef.compid = ca.compid   " +
			" LEFT JOIN zdd_crowdsource_company_party ccp on ccp.id = cef.party_id " +
			" LEFT JOIN zdd_business_manager bm ON ca.merchandiser_id = bm.business_manager_id " +
			" LEFT JOIN zdd_business_manager zbm ON ca.business_manager_id = zbm.business_manager_id "+
			" where if(ISNULL(#{compId}) or #{compId} = 0, 1=1 ,cef.compid = #{compId})" +
			" and if(ISNULL(#{compname}), 1=1, c.compname like '%${compname}%') " +
			" and if(ISNULL(#{from}), 1=1, cef.entrust_end_time >= #{from})" +
			" and if(ISNULL(#{to}), 1=1, cef.entrust_end_time <= #{to})" +
			" and if(#{source} = 0,1=1,cef.source=#{source})" +
			" and if(#{workType} = 0,1=1,cef.work_type=#{workType})" +
			" and cef.deleted = 0" +
			" ${orderBy} ")
	List<CompanyEntrustFile> listByCompnameDate(Integer compId,String compname, Date from, Date to,int source,int workType,String orderBy);

	@Select(" select cef.*, c.compname " +
			" from zdd_company_entrust_file cef " +
			" join zdd_company c on c.compid = cef.compid " +
			" where cef.entrust_end_time = #{endDate} " +
			" AND c.state = 4 " +
			" and cef.audit_state = 2" +
			" and cef.deleted = 0" +
			" order by entrust_end_time desc")
	List<CompanyEntrustFile> listByEndDate(String endDate);

	@Select("select cef.*, c.compname   " +
			"from zdd_company_entrust_file cef   " +
			"join zdd_company c on c.compid = cef.compid " +
			"JOIN (SELECT MAX(entrust_end_time) AS endTime, id FROM zdd_company_entrust_file where state = 1 GROUP BY compid) cef2 ON cef2.id = cef.id " +
			"where cef2.endTime < NOW() " +
			" AND c.state = 4 " +
			" and cef.audit_state = 2" +
			" and cef.deleted = 0" +
			" and c.intel_crowdsource = 2 ")
	List<CompanyEntrustFile> beExpired();

	@Update("UPDATE zdd_company_entrust_file set state = 3 WHERE audit_state = 2 and state = 1 and deleted = 0 and entrust_end_time < NOW() ")
	void refreshState();

	@Select("SELECT " +
			" c.* " +
			"FROM " +
			" `zdd_company` c " +
			"LEFT JOIN zdd_company_entrust_file ef ON ef.compid = c.compid " +
			"WHERE ef.id is NULL " +
			"AND(c.open_odd_job = 1 OR c.intel_crowdsource = 2 )" +
			"and c.state = 4 ")
	List<Company> theContractWasNotUploaded();


	@Select(" select " +
			" cef.*," +
			" ccp.company_name as partyName" +
			" FROM zdd_company_entrust_file cef " +
			" LEFT JOIN zdd_crowdsource_company_party ccp on ccp.id = cef.party_id " +
			" where cef.compid = #{compId}" +
			" and cef.deleted = 0 " +
			" order by cef.id desc" )
	List<CompanyEntrustFile> getAnEnterpriseContract(@Param("compId") Integer compId);

}
